package com.platform.helper.office.excel;

import com.platform.helper.bean.MapperUtil;
import com.platform.helper.date.DateFormatEnum;
import com.platform.helper.date.DateUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.FileMagic;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.commons.collections4.CollectionUtils;

import java.io.*;
import java.util.*;

/**
 * describe:Excel工具类
 *
 * @author rainly
 * @date 2020/4/2
 * @time 10:32 上午
 */
@Slf4j
public class ExcelReadUtil {

    /**
     * 数据类型
     */
    private enum ListType{
        /**
         * 工作表
         */
        SHEET,
        /**
         * 行
         */
        ROW,
        /**
         * 列
         */
        COLUMN
    }

    /**
     * 通过魔数判断是否是Excel文件
     * @param inputStream inputStream
     * @return boolean
     */
    public static boolean isExcelFile(BufferedInputStream inputStream){
        try {
            FileMagic fileMagic = FileMagic.valueOf(inputStream);
            return Objects.equals(fileMagic,FileMagic.OLE2) || Objects.equals(fileMagic,FileMagic.OOXML);
        } catch (IOException e) {
            log.error("excel工具类",e);
        }
        return Boolean.FALSE;
    }
    /**
     * 判断是否是Excel文件
     * @param file 文件
     * @return boolean
     */
    public static boolean isExcelFile(File file){
        try {
            FileMagic fileMagic = FileMagic.valueOf(file);
            return Objects.equals(fileMagic,FileMagic.OLE2) || Objects.equals(fileMagic,FileMagic.OOXML);
        } catch (IOException e) {
            log.error("excel工具类",e);
        }
        return Boolean.FALSE;
    }

    /**
     * 读取Excel
     * @param inputStream BufferedInputStream
     * @return List<List<List<String>>>
     */
    public static List<List<List<String>>> readExcel(BufferedInputStream inputStream){
        if (!isExcelFile(inputStream)){
            log.warn("读取的文件不是Excel文件格式");
            return new ArrayList<>(0);
        }
        try {
            FileMagic fileMagic = FileMagic.valueOf(inputStream);
            Workbook workbook = null;
            //判断是否是xls文件
            if (Objects.equals(fileMagic,FileMagic.OLE2)){
                workbook = new HSSFWorkbook(inputStream);
            }
            //判断是否是xlsx文件
            if (Objects.equals(fileMagic,FileMagic.OOXML)){
                workbook = new XSSFWorkbook(inputStream);
            }
            if (workbook == null){
                return new ArrayList<>(0);
            }
            List<List<List<String>>> sheetList = new ArrayList<>(workbook.getNumberOfSheets());
            // 循环工作表Sheet
            for (int numSheet = 0; numSheet < workbook.getNumberOfSheets(); numSheet++) {
                Sheet sheet = workbook.getSheetAt(numSheet);
                if (sheet != null) {
                    List<CellRangeAddress> cellRangeAddressList = new ArrayList<>(sheet.getNumMergedRegions());
                    //获得合并单元格
                    for (int mergerNum = 0; mergerNum < sheet.getNumMergedRegions(); mergerNum++) {
                        // 获得合并单元格保存进list中
                        cellRangeAddressList.add(sheet.getMergedRegion(mergerNum));
                    }

                    List<List<String>> rowList = new ArrayList<>(sheet.getLastRowNum());
                    // 循环行Row 总行数等于 getEndRowNum+1，所以用小于等于
                    for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {
                        Row row = sheet.getRow(rowNum);
                        if (row != null) {
                            List<String> cellList = new ArrayList<>(row.getLastCellNum());
                            //循环列column
                            for (int cellNum = 0;cellNum < row.getLastCellNum(); cellNum++ ){
                                //获得单元格
                                Cell cell = row.getCell(cellNum);
                                String cellStr = convertCell(cell);
                                cellList.add(cellStr);
                            }
                            rowList.add(cellList);
                        }
                    }
                    //拆分合并行
                    convertMergerCell(cellRangeAddressList,rowList);
                    sheetList.add(rowList);
                }
            }
            return sheetList;
        } catch (IOException e) {
            log.error("Excel文件读取异常",e);
        }
        return new ArrayList<>(0);
    }

    /**
     * 读取Excel
     * @param file 文件
     * @return List<List<List<String>>>
     */
    public static List<List<List<String>>> readExcel(File file){
        try {
            if (!isExcelFile(file)){
                return new ArrayList<>(0);
            }
            return readExcel(new BufferedInputStream(new FileInputStream(file)));
        } catch (FileNotFoundException e) {
            log.error("读取Excel文件失败",e);
        }
        return new ArrayList<>(0);
    }

    /**
     * 读取Excel
     * @param pathname 路径以及文件名
     * @return List<List<List<String>>>
     */
    public static List<List<List<String>>> readExcel(String pathname){
        File file = new File(pathname);
        if (file.exists()){
            if (!isExcelFile(file)){
                return new ArrayList<>(0);
            }
            return readExcel(file);
        }
        return new ArrayList<>(0);
    }

    /**
     * 解析单元格
     * @param cell 单元格
     * @return String
     */
    private static String convertCell(Cell cell){
        if(null == cell){
            return null;
        }
        String returnValue;
        switch (cell.getCellType()) {
            //数字
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)){
                    Date cellDate = cell.getDateCellValue();
                    returnValue = DateUtils.dateToString(cellDate, DateFormatEnum.TIME);
                }else{
                    double doubleValue = cell.getNumericCellValue();
                    //将cell类型设置成字符串
                    returnValue = Double.toString(doubleValue);
                }
                break;
            //字符串
            case STRING:
                returnValue = cell.getStringCellValue();
                break;
            //布尔
            case BOOLEAN:
                boolean booleanValue = cell.getBooleanCellValue();
                returnValue = Boolean.toString(booleanValue);
                break;
            // 公式
            case FORMULA:
                returnValue = cell.getCellFormula();
                break;
            // 空值
            case BLANK:
            // 故障
            case ERROR:
            // 默认
            default:
                returnValue = "";
                break;
        }
        return returnValue;
    }

    /**
     * 解析合并单元格
     * @param cellRangeAddressList 合并单元格的list
     * @param rowList sheet解析的数据
     */
    private static void convertMergerCell(List<CellRangeAddress> cellRangeAddressList,List<List<String>> rowList){
        if (CollectionUtils.isEmpty(cellRangeAddressList)){
            return;
        }
        for (CellRangeAddress cellAddresses: cellRangeAddressList) {
            int beginRow = cellAddresses.getFirstRow();
            int endRow = cellAddresses.getLastRow();

            int beginColumn = cellAddresses.getFirstColumn();
            int endColumn = cellAddresses.getLastColumn();
            //合并单元格的数据
            String tempStr = rowList.get(beginRow).get(beginColumn);
            //将空白单元格填入数据
            while (beginRow <= endRow) {
                for (int i = beginColumn; i <= endColumn; i++) {
                    rowList.get(beginRow).set(i,tempStr);
                }
                endRow++;
            }
        }
    }

    /**
     * 多重对象Map映射
     * @param tempList Excel数据
     * @param excelBeans Excel对象List
     */
    public static List<List<?>> toMultipleClass(List<List<List<String>>> tempList,List<ExcelReadBean> excelBeans){
        List<List<?>> list = new ArrayList<>(excelBeans.size());
        for (ExcelReadBean excelBean: excelBeans) {
            list.add(toSingleClass(tempList, excelBean));
        }
        return list;
    }

    /**
     * 单一对象Map映射
     * @param tempList Excel数据
     * @param excelBean Excel对象
     */
    public static List<?> toSingleClass(List<List<List<String>>> tempList, ExcelReadBean excelBean){
        List<List<Map<String,String>>> allData = toMap(tempList,excelBean);
        List<Map<String,String>> list = new ArrayList<>();
        for (List<Map<String,String>> data: allData) {
            list.addAll(data);
        }
        if (null == excelBean.getClazz()){
            return list;
        }else{
            List<Object> clazzList = new ArrayList<>();
            for (Map<String,String> map: list) {
                //clazzList.add(excelBean.mapToObject(map));
                clazzList.add(MapperUtil.map(map, excelBean.getClass()));
            }
            return clazzList;
        }
    }

    /**
     * list数据转Map对象
     * @param tempList Excel数据
     * @param excelBean Excel对象
     * @return List<List<Map<String,String>>>
     */
    private static List<List<Map<String,String>>> toMap(List<List<List<String>>> tempList, ExcelReadBean excelBean){
        if(CollectionUtils.isEmpty(tempList)){
            return new ArrayList<>(0);
        }
        List<List<Map<String,String>>> allData = null;
        switch (excelBean.getDataVector()){
            case ROW:
                allData = toMapByVectorRow(tempList,excelBean);
                break;
            case COLUMN:
                allData = toMapByVectorColumn(tempList,excelBean);
                break;
            default:
                break;
        }
        return allData;
    }

    /**
     * 数据方向为行方向读取
     * @param tempList 数据
     * @param excelBean 对象
     * @return List<List<Map<String,String>>>
     */
    private static List<List<Map<String,String>>> toMapByVectorRow(List<List<List<String>>> tempList, ExcelReadBean excelBean){
        checkBean(tempList, excelBean, ListType.SHEET);
        List<List<Map<String,String>>> allData = new ArrayList<>();
        for (int sheetNum = excelBean.getBeginSheetNum(); sheetNum < excelBean.getEndSheetNum(); sheetNum++) {
            if(CollectionUtils.isEmpty(tempList.get(sheetNum))){
                continue;
            }
            checkBean(tempList.get(sheetNum), excelBean, ListType.ROW);
            List<Map<String,String>> mapList = new ArrayList<>(excelBean.getEndRowNum());
            for (int rowNum = excelBean.getBeginRowNum(); rowNum < excelBean.getEndRowNum(); rowNum++) {
                if(CollectionUtils.isEmpty(tempList.get(sheetNum).get(rowNum))){
                    continue;
                }
                checkBean(tempList.get(sheetNum).get(rowNum), excelBean, ListType.COLUMN);
                int keyNum = 0;
                Map<String,String> map = new HashMap<>(excelBean.getEndColumnNum());
                for (int columnNum = excelBean.getBeginColumnNum(); columnNum < excelBean.getEndColumnNum(); columnNum++) {
                    String val = tempList.get(sheetNum).get(rowNum).get(columnNum);
                    map.put(excelBean.getKeys().get(keyNum),val);
                    if (keyNum >= excelBean.getKeys().size()-1){
                        break;
                    }
                    keyNum++;
                }
                mapList.add(map);
            }
            allData.add(mapList);
        }
        return allData;
    }

    /**
     * 数据方向为列方向读取
     * @param tempList 数据
     * @param excelBean 对象
     * @return List<List<Map<String,String>>>
     */
    private static List<List<Map<String,String>>> toMapByVectorColumn(List<List<List<String>>> tempList, ExcelReadBean excelBean){
        checkBean(tempList, excelBean, ListType.SHEET);
        List<List<Map<String,String>>> allData = new ArrayList<>();
        for (int sheetNum = excelBean.getBeginSheetNum(); sheetNum < excelBean.getEndSheetNum(); sheetNum++) {
            if (CollectionUtils.isEmpty(tempList.get(sheetNum))){
                continue;
            }
            checkBean(tempList.get(sheetNum), excelBean, ListType.ROW);
            List<Map<String,String>> mapList = new ArrayList<>(excelBean.getEndRowNum());
            int keyNum = 0;
            for (int rowNum = excelBean.getBeginRowNum(); rowNum < excelBean.getEndRowNum(); rowNum++) {
                if(CollectionUtils.isEmpty(tempList.get(sheetNum).get(rowNum))){
                    continue;
                }
                checkBean(tempList.get(sheetNum).get(rowNum), excelBean, ListType.COLUMN);
                for (int columnNum = excelBean.getBeginColumnNum(); columnNum < excelBean.getEndColumnNum(); columnNum++) {
                    String val = tempList.get(sheetNum).get(rowNum).get(columnNum);
                    Map<String,String> map;
                    if ((columnNum - excelBean.getBeginColumnNum()) >= mapList.size()){
                        map = new HashMap<>(excelBean.getEndRowNum());
                        mapList.add(map);
                    }else{
                        map = mapList.get(columnNum - excelBean.getBeginColumnNum());
                    }
                    map.put(excelBean.getKeys().get(keyNum),val);
                }
                if (keyNum >= excelBean.getKeys().size()-1){
                    break;
                }
                keyNum++;
            }
            allData.add(mapList);
        }
        return allData;
    }

    /**
     * 校验ExcelBean对象参数
     * @param list 数据
     * @param excelBean Excel对象
     * @param listType 数据类型
     */
    private static void checkBean(List<?> list, ExcelReadBean excelBean, ListType listType){
        if (null == list){
            return;
        }
        //数据处理
        int maxNum = list.size();
        int endNum;
        switch (listType){
            case SHEET:
                if (null == excelBean.getEndSheetNum()){
                    excelBean.setEndSheetNum(maxNum);
                    break;
                }
                endNum = excelBean.getEndSheetNum();
                if (endNum > maxNum){
                    excelBean.setEndSheetNum(maxNum);
                }
                break;
            case ROW:
                if (null == excelBean.getEndRowNum()){
                    excelBean.setEndRowNum(maxNum);
                    break;
                }
                endNum = excelBean.getEndRowNum();
                if (endNum > maxNum){
                    excelBean.setEndRowNum(maxNum);
                }
                break;
            case COLUMN:
                if (null == excelBean.getEndColumnNum()){
                    excelBean.setEndColumnNum(maxNum);
                    break;
                }
                endNum = excelBean.getEndColumnNum();
                if (endNum > maxNum){
                    excelBean.setEndColumnNum(maxNum);
                }
                break;
            default:
                break;
        }
    }
}
